# -*- coding: utf-8 -*-
"""
Created on Mon Jul 15 09:00:47 2019

@author: R720-15
"""

from pandas import *
from matplotlib.pyplot import *
import sqlite3
import sqlalchemy
from numpy import *
import math
from functools import reduce
import matplotlib
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D

#计算欧氏距离
def distEclud(vecA, vecB):
    return sqrt(sum(power(vecA - vecB, 2))) #la.norm(vecA-vecB)

def randCent(dataSet, k):
    n = shape(dataSet)[1]
    centroids = mat(zeros((k,n)))#create centroid mat
    for j in range(n):#create random cluster centers, within bounds of each dimension
        minJ = min(dataSet[:,j]) 
        rangeJ = float(max(dataSet[:,j]) - minJ)
        centroids[:,j] = mat(minJ + rangeJ * random.rand(k,1))
    return centroids

#str转float
def str2float(s):
    def fn(x,y):
        return x*10+y
    if(s.find('.')!=-1):
        n=s.find('.')
        s1=list(map(int,[x for x in s[:n]]))
        s2=list(map(int,[x for x in s[n+1:]]))
        return reduce(fn,s1)+reduce(fn,s2)/(10**len(s2))#乘幂
    else:
        if(s==''):
            print('***')
        else:
            return float(int(s))

#归一化处理
def autoNorm(dataSet):
    minVals = dataSet.min(0)
    maxVals = dataSet.max(0)
    ranges = maxVals - minVals
    normDataSet = zeros(shape(dataSet))
    m = dataSet.shape[0]
    normDataSet = dataSet - tile(minVals, (m,1))
    normDataSet = normDataSet/tile(ranges, (m,1))   #element wise divide
    return normDataSet

#K均值聚类
def biKmeans(dataSet, k, distMeas=distEclud):
    m = shape(dataSet)[0]
    clusterAssment = mat(zeros((m,2)))
    centroid0 = mean(dataSet, axis=0).tolist()[0]
    centList =[centroid0] #create a list with one centroid
    for j in range(m):#calc initial Error
        clusterAssment[j,1] = distMeas(mat(centroid0), dataSet[j,:])**2
    while (len(centList) < k):
        lowestSSE = inf
        for i in range(len(centList)):
            ptsInCurrCluster = dataSet[nonzero(clusterAssment[:,0].A==i)[0],:]#get the data points currently in cluster i
            centroidMat, splitClustAss = kMeans(ptsInCurrCluster, 2, distMeas)
            sseSplit = sum(splitClustAss[:,1])#compare the SSE to the currrent minimum
            sseNotSplit = sum(clusterAssment[nonzero(clusterAssment[:,0].A!=i)[0],1])
            print ("sseSplit, and notSplit: ",sseSplit,sseNotSplit)
            if (sseSplit + sseNotSplit) < lowestSSE:
                bestCentToSplit = i
                bestNewCents = centroidMat
                bestClustAss = splitClustAss.copy()
                lowestSSE = sseSplit + sseNotSplit
        bestClustAss[nonzero(bestClustAss[:,0].A == 1)[0],0] = len(centList) #change 1 to 3,4, or whatever
        bestClustAss[nonzero(bestClustAss[:,0].A == 0)[0],0] = bestCentToSplit
        print ('the bestCentToSplit is: ',bestCentToSplit)
        print ('the len of bestClustAss is: ', len(bestClustAss))
        centList[bestCentToSplit] = bestNewCents[0,:].tolist()[0]#replace a centroid with two best centroids 
        centList.append(bestNewCents[1,:].tolist()[0])
        clusterAssment[nonzero(clusterAssment[:,0].A == bestCentToSplit)[0],:]= bestClustAss#reassign new clusters, and SSE
    return mat(centList), clusterAssment

#绘图
def clusterClubs(dataMat,numClust=5):

    myCentroids, clustAssing = biKmeans(dataMat, numClust)
    fig = plt.figure()
    rect=[0.1,0.1,0.8,0.8]
    scatterMarkers=['s', 'o', '^', '8', 'p', \
                    'd', 'v', 'h', '>', '<']
    axprops = dict(xticks=[], yticks=[])
    ax0=fig.add_axes(rect, label='ax0', **axprops)
    imgP = plt.imread('Portland.png')
    ax0.imshow(imgP)
    ax1 = plt.subplot(111, projection='3d')  # 创建一个三维的绘图工程
    
    for i in range(numClust):
        ptsInCurrCluster = dataMat[nonzero(clustAssing[:,0].A==i)[0],:]
        markerStyle = scatterMarkers[i % len(scatterMarkers)]
        ax1.scatter(ptsInCurrCluster[:,0].flatten().A[0], ptsInCurrCluster[:,1].flatten().A[0], ptsInCurrCluster[:,2].flatten().A[0], marker=markerStyle, s=90)
    ax1.scatter(myCentroids[:,0].flatten().A[0], myCentroids[:,1].flatten().A[0], myCentroids[:,2].flatten().A[0], marker='+', s=300)
    ax1.view_init(elev=20,azim=45)#改变绘制图像的视角
    ax1.set_xlabel('折扣')
    ax1.set_ylabel('评论数')
    ax1.set_zlabel('！！钱')
    plt.show()

engine = sqlalchemy.create_engine('sqlite:///bookNew2.sqlite')
rcParams['font.sans-serif'] = ['SimHei']
options.display.float_format = '{:,.2f}%'.format

sales = read_sql('select name,author,discount,commentNum,price from t_sales',engine)
sals_mat=sales.values 
#size1size2Count = sales.groupby(['discount','commentNum','price','name','author'])['author'].count()
sals_list=[]
for item in sals_mat:
    cache=[]
    if(item[2]!='' and item[3]!='' and item[4]!=''):
        cache.append(str2float(item[2]))
        cache.append(float(int(item[3])/10000))
        cache.append(str2float(item[4]))
        sals_list.append(cache)
dataMat=autoNorm(mat(sals_list))
#print(randCent(dataMat,2))
#print(distEclud(dataMat[0],dataMat[1]))
#print(dataMat[0])
clusterClubs(dataMat)
#print(myCentroids)
'''
size1size2Total = size1size2Count.sum()
print(size1size2Total)
size1size2 = size1size2Count.to_frame(name='销量')
n = 500
# 过滤出销量小等于500的组，并统计这些组的总销量，将统计结果放到DataFrame中
others = DataFrame([size1size2[size1size2['销量'] <= 
     n].sum()],index=MultiIndex(levels=[[''],['其他']],labels=[[0],[0]]))


# 将“其他”销量放到记录集的最后
size1size2 = size1size2[size1size2['销量']>n].append(others)
print(size1size2)

size1size2 = size1size2.sort_values(['销量'],ascending=[0])
size1size2.insert(0,'比例',100 * size1size2Count / size1size2Total)
print(size1size2)
labels = size1size2.index.tolist()
newLabels = []
# 生成饼图外侧显示的每一部分的表示（如75B、80A等）
for label in labels:
    newLabels.append(label[1] + label[0])
pie(size1size2['销量'],labels=newLabels,autopct='%.2f%%')
legend()
axis('equal')
title('罩杯+上胸围销售比例')
show()
'''